﻿-- Navod ziskaz ne stranky:
-- http://jacobmsaylor.com/?p=837
-- nebo tady: http://www.thegeekstuff.com/2010/10/postgresql-trigger-tutorial-with-emp-table-examples/
-- http://michael.otacoo.com/postgresql-2/postgres-trigger-for-beginners/

DROP TRIGGER IF EXISTS insert_hodnoceni_trigger ON hodnoceni;
DROP FUNCTION IF EXISTS insert_hodnoceni_procedure();

-- Nejdrive se musi vytvorit procedura.
CREATE OR REPLACE FUNCTION insert_hodnoceni_procedure() RETURNS TRIGGER AS $hodnoceni$
    BEGIN
        IF (TG_OP = 'INSERT') THEN         
            UPDATE film SET
                ohodnoceni = (SELECT avg(hodnotil) FROM hodnoceni WHERE id_filmu=NEW.id_filmu)
                WHERE id_filmu = NEW.id_filmu ;
        END IF;
        RETURN NEW;
    END;
$hodnoceni$ LANGUAGE plpgsql;

-- Kdyz mame vytvorenou proceduru, tak konecne muzeme vytvorit trigger, ktery poroceduru vyuzije.

CREATE TRIGGER insert_hodnoceni_trigger AFTER INSERT ON hodnoceni
    FOR EACH ROW EXECUTE PROCEDURE insert_hodnoceni_procedure();